"""Follow-up reminder repository."""

from __future__ import annotations

from datetime import datetime, timedelta
from sqlalchemy import select, and_, or_
from sqlalchemy.orm import Session, joinedload

from ..models.follow_up_reminder import FollowUpReminder
from ..models.lead import Lead
from ..models.user import User
from ..schemas.follow_up_reminder import FollowUpReminderCreate, FollowUpReminderUpdate


class FollowUpReminderRepository:
    """跟进提醒Repository."""

    def __init__(self, db: Session):
        """Initialize repository."""
        self.db = db

    def create(self, reminder: FollowUpReminderCreate) -> FollowUpReminder:
        """创建提醒."""
        db_obj = FollowUpReminder(**reminder.model_dump())
        self.db.add(db_obj)
        self.db.flush()
        return db_obj

    def get(self, reminder_id: int) -> FollowUpReminder | None:
        """获取单个提醒."""
        stmt = (
            select(FollowUpReminder)
            .where(FollowUpReminder.id == reminder_id)
            .options(
                joinedload(FollowUpReminder.lead),
                joinedload(FollowUpReminder.assignee)
            )
        )
        return self.db.execute(stmt).scalar_one_or_none()

    def get_by_assigned_to(
        self,
        assigned_to: int,
        status: str | None = None,
        skip: int = 0,
        limit: int = 50
    ) -> tuple[list[FollowUpReminder], int]:
        """获取分配给某人的提醒（带总数）."""
        # Base query
        stmt = (
            select(FollowUpReminder)
            .where(FollowUpReminder.assigned_to == assigned_to)
            .options(
                joinedload(FollowUpReminder.lead),
                joinedload(FollowUpReminder.assignee)
            )
        )

        # Filter by status
        if status:
            stmt = stmt.where(FollowUpReminder.status == status)

        # Count total
        count_stmt = select(FollowUpReminder).where(
            FollowUpReminder.assigned_to == assigned_to
        )
        if status:
            count_stmt = count_stmt.where(FollowUpReminder.status == status)
        total = len(self.db.execute(count_stmt).scalars().all())

        # Apply pagination and ordering
        stmt = (
            stmt
            .order_by(FollowUpReminder.reminder_time.asc())
            .offset(skip)
            .limit(limit)
        )

        results = list(self.db.execute(stmt).scalars().all())
        return results, total

    def get_today_tasks(self, assigned_to: int) -> list[FollowUpReminder]:
        """获取今日待办任务."""
        today_start = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        today_end = today_start + timedelta(days=1)

        stmt = (
            select(FollowUpReminder)
            .where(
                and_(
                    FollowUpReminder.assigned_to == assigned_to,
                    FollowUpReminder.status == "pending",
                    FollowUpReminder.reminder_time < today_end
                )
            )
            .options(
                joinedload(FollowUpReminder.lead),
                joinedload(FollowUpReminder.assignee)
            )
            .order_by(FollowUpReminder.reminder_time.asc())
        )

        return list(self.db.execute(stmt).scalars().all())

    def get_pending_reminders_to_send(self) -> list[FollowUpReminder]:
        """获取需要发送的待处理提醒（提醒时间已到，状态为pending）."""
        now = datetime.now()

        stmt = (
            select(FollowUpReminder)
            .where(
                and_(
                    FollowUpReminder.status == "pending",
                    FollowUpReminder.reminder_time <= now,
                    FollowUpReminder.sent_at.is_(None)  # 未发送过邮件
                )
            )
            .options(
                joinedload(FollowUpReminder.lead),
                joinedload(FollowUpReminder.assignee)
            )
            .order_by(FollowUpReminder.reminder_time.asc())
        )

        return list(self.db.execute(stmt).scalars().all())

    def update(self, reminder_id: int, update_data: FollowUpReminderUpdate) -> FollowUpReminder | None:
        """更新提醒."""
        db_obj = self.get(reminder_id)
        if not db_obj:
            return None

        for field, value in update_data.model_dump(exclude_unset=True).items():
            setattr(db_obj, field, value)

        db_obj.updated_at = datetime.now()
        self.db.flush()
        return db_obj

    def mark_as_sent(self, reminder_id: int) -> FollowUpReminder | None:
        """标记为已发送."""
        db_obj = self.get(reminder_id)
        if not db_obj:
            return None

        db_obj.status = "sent"
        db_obj.sent_at = datetime.now()
        db_obj.updated_at = datetime.now()
        self.db.flush()
        return db_obj

    def mark_as_completed(self, reminder_id: int) -> FollowUpReminder | None:
        """标记为已完成."""
        db_obj = self.get(reminder_id)
        if not db_obj:
            return None

        db_obj.status = "completed"
        db_obj.completed_at = datetime.now()
        db_obj.updated_at = datetime.now()
        self.db.flush()
        return db_obj

    def delete(self, reminder_id: int) -> bool:
        """删除提醒."""
        db_obj = self.get(reminder_id)
        if not db_obj:
            return False

        self.db.delete(db_obj)
        self.db.flush()
        return True

    def auto_create_visitor_sheet_reminder(
        self,
        visitor_sheet_id: int,
        lead_id: int,
        assigned_to: int,
        days_after: int = 3
    ) -> FollowUpReminder:
        """自动创建访客单跟进提醒（看房N天后）."""
        reminder_time = datetime.now() + timedelta(days=days_after)

        reminder = FollowUpReminderCreate(
            lead_id=lead_id,
            visitor_sheet_id=visitor_sheet_id,
            reminder_type="auto",
            trigger_reason=f"看房{days_after}天后自动提醒",
            reminder_time=reminder_time,
            assigned_to=assigned_to,
            notes=f"系统自动创建：客户已看房{days_after}天，建议主动回访了解意向"
        )

        return self.create(reminder)
